#!/usr/bin/env python
# coding:utf-8

import MySQLdb, sys

reload(sys)
sys.setdefaultencoding('utf8')

# sql=sys.argv[1]

def select_mysql(sql):
    conn = MySQLdb.connect(host='10.0.0.142', user='daxmall_r', passwd='2hGUnCKoEuvkWNgRbvwo', db='daxmall',
                           charset='UTF8')

    cur = conn.cursor()
    reCount = cur.execute(sql)

    nRet = cur.fetchall()
    cur.close()

    conn.close()
    return nRet


def insert_mysql(sql):
    conn = MySQLdb.connect(host='121.201.68.21', port=3307, user='jiang', passwd='jiangwenhui', db='daxiangzhanshi',

                           charset='UTF8')

    cur = conn.cursor()

    cur.execute(sql)
    nRet = cur.fetchall()
    conn.commit()

    cur.close()

    conn.close()
    return nRet

if __name__ == "__main__":
    insert_mysql("delete from app01_city_trading_volume")
    select_ls_category = "SELECT r.city_id AS 城市ID,COUNT(1) AS 订单数量,SUM(s.product_nums) AS 商品数量,SUM(s.total) AS 订单总金额  FROM ls_sub s      INNER JOIN ls_usr_addr_sub a ON a.`addr_order_id`=s.`addr_order_sn`      INNER JOIN ls_usr_addr r ON r.`addr_id`=a.`addr_id`  WHERE s.update_date>=curdate() AND s.status != 5  GROUP BY r.city_id;"
    data = select_mysql(select_ls_category)
    for i in data:
#	print i
        insert_ls_category = '''insert into app01_city_trading_volume (city_name,city_rmb,updatetime) values ('%s','%s',curdate()) '''%(i[0],i[1])
        insert_ls_category=insert_ls_category.replace("市",'')
        insert_mysql(insert_ls_category)
        #if not insert_mysql("select * from app01_real where updatetime='%s'"%i[3]):
            #insert_mysql(insert_ls_category)
	#print(insert_ls_category)
